Example 6 - Build an outlined schedule from an Excel spreadsheet

Public Sub CreateOutlinedSchedule()

' this function updates the schedule using data from a spreadsheet table

' it refers to sheet 2 of the current workbook

Dim numberoftasklines As Integer

Dim numberofsymbols As Integer

Dim x As Integer

Dim x2 As Integer

Dim TaskNumber As Integer

Dim earliestday As Date

Dim latestday As Date

Dim newdate As Date

Dim temp As Date

Dim outlinelevel As Integer

'Create a new Milestones Professional schedule

Set objMilestones = CreateObject("Milestones")

'Start using the new schedule object

With objMilestones

 ' Activate Milestones Professional Schedule

.Activate

'initialize earliestday and latestday variables.  Use these later to set the schedule

'start and end dates

earliestday = "12/31/2099"

latestday = "1/1/1900"

'load in a template  If an error message occurs at this point it means that the template is

'not located in the user's default template folder

.Template "ExcelTemplate1.mtp" 'visit our programmer's page to get this template

   'Loop through and build the schedule using information from the spreadsheet

For TaskNumber = 1 To 17

     .PutCell TaskNumber, 1, Worksheets("Sheet2").Cells(TaskNumber + 1, 1)

     .SetOutlineLevel TaskNumber, Worksheets("Sheet2").Cells(TaskNumber + 1, 2).Value

      OutlineLevel = Worksheets("Sheet2").Cells(TaskNumber + 1, 2).Value

      If OutlineLevel < 2 Then

        GoTo SkipEndDate

      End If

      On Error GoTo SkipStartDate

      .AddSymbol TaskNumber, Format(Worksheets("Sheet2").Cells(TaskNumber + 1, 3), "mm/dd/yy"), 1, 1, 2

SkipStartDate:

      On Error GoTo SkipEndDate

      temp = Worksheets("Sheet2").Cells(TaskNumber + 1, 4)

      temp = Format(Worksheets("Sheet2").Cells(TaskNumber + 1, 4), "mm/dd/yy")

      If temp > Format("1/1/90", "mm/dd/yy") Then

      .AddSymbol TaskNumber, temp, 2, 1, 2

      End If

      'compare task start date to current schedule start/end date

      newdate = (Worksheets("Sheet2").Cells(TaskNumber + 1, 3))

      If newdate < earliestday Then

      earliestday = newdate

      End If

      If newdate > latestday Then

      latestday = newdate

      End If

      'compare task start date to current schedule start/end date

      newdate = (Worksheets("Sheet2").Cells(TaskNumber + 1, 4))

      If newdate < earliestday Then

      earliestday = newdate

  End If

      If newdate > latestday Then

        latestday = newdate

      End If

SkipEndDate:

      .Refresh

        Next

      .SetTitle1 "EXCEL SCHEDULE EXAMPLE"

      .SetTitle2 "Milestones Professional"

      .SetTitle3 "OLE Automation"

      .SetStartDate earliestday

      .SetEndDate latestday

      .Refresh

      .KeepScheduleOpen

End With  

Exit Sub

End Sub